﻿
-- 监视点卡权限信息
create procedure [dbo].[JsdCaqx_Get]
@QuerryType bit =null, -- null:查询所有 0：只查询无监视点的人员 1：只查询有监视点的人员 
@kh varchar(10) = null,
@kbs varchar(20) = null,
@jsdbh varchar(18) = null
as
if(@QuerryType is null) -- 查询所有
begin
	select kh,kbs,Jsd_Info.Jsdbh,Jsdm,sfhbmd,case sfhbmd when 0 then '否' else '是' end sfhbmd_desc, 
	sfkqfq,case sfkqfq when 0 then '否' else '是' end sfkqfq_desc,yxjcsj from Client_Ca_Info
	left join JsdRyqx_Info on (kh=sfzhhkh)
	left join Jsd_Info on(Jsd_Info.Jsdbh=JsdRyqx_Info.Jsdbh)
	and (@kh is null or kh=@kh)
	and (@kbs is null or kbs like @kbs+'%')
	and (@Jsdbh is null or JsdRyqx_Info.Jsdbh like @Jsdbh+'%')
end
else if(@QuerryType=0)-- 只查询无监视点
begin
	select kh,kbs from Client_Ca_Info
	where sfyx=1 and kh not in 
	(select sfzhhkh from JsdRyqx_Info where @jsdbh is null or jsdbh like @jsdbh+'%')
	and (@kh is null or kh=@kh)
	and (@kbs is null or kbs like @kbs+'%')
end
else if(@QuerryType=1)-- 只查询有监视点
begin
	select kh,kbs,Jsd_Info.Jsdbh,Jsdm,sfhbmd,case sfhbmd when 0 then '否' else '是' end sfhbmd_desc, 
	sfkqfq,case sfkqfq when 0 then '否' else '是' end sfkqfq_desc,yxjcsj from Client_Ca_Info
	left join JsdRyqx_Info on (kh=sfzhhkh)
	left join Jsd_Info on(Jsd_Info.Jsdbh=JsdRyqx_Info.Jsdbh)
	where JsdRyqx_Info.jsdbh is not null
	and (@kh is null or kh=@kh)
	and (@kbs is null or kbs like @kbs+'%')
	and (@Jsdbh is null or JsdRyqx_Info.Jsdbh like @Jsdbh+'%')
end